{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "#显示所有列\n",
    "pd.set_option('display.max_columns', None)\n",
    "#显示所有行\n",
    "pd.set_option('display.max_rows', None)\n",
    "#设置value的显示长度为100，默认为50\n",
    "pd.set_option('max_colwidth',100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"id\":[1001,1002,1003,1004,1005,1006], \n",
    "        \"date\":pd.date_range('20130102', periods=6),\n",
    "        \"city\":['Beijing ', 'SH', ' guangzhou ', 'Shenzhen', 'shanghai', 'BEIJING '],\n",
    "        \"age\":[23,44,54,32,34,45],\n",
    "        \"category\":['100-A','100-B','110-A','110-C','210-A','130-F'],\n",
    "        \"price\":[1200.9,np.nan,2133,5433.2,np.nan,4432]\n",
    "    },\n",
    "    columns =['id','date','city','category','age','price'])\n",
    "\n",
    "df1 = pd.DataFrame({\n",
    "        \"id\":[1001,1002,1003,1004,1005,1006,1007,108], \n",
    "        \"gender\":['male','female','male','female','male','female','male','female'],\n",
    "        \"pay\":['Y','N','Y','Y','N','Y','N','Y',],\n",
    "        \"m-point\":[10,12,20,40,40,40,30,20]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 一、信息表信息查看"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "## 1、查看前2行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>Beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>SH</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date      city category  age   price\n",
       "0  1001 2013-01-02  Beijing     100-A   23  1200.9\n",
       "1  1002 2013-01-03        SH    100-B   44     NaN"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2、查看后2行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45</td>\n",
       "      <td>4432.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date      city category  age   price\n",
       "4  1005 2013-01-06  shanghai    210-A   34     NaN\n",
       "5  1006 2013-01-07  BEIJING     130-F   45  4432.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3、维度查看"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6, 6)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4、数据表基本信息（维度、列名称、数据格式、所占空间等）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 6 entries, 0 to 5\n",
      "Data columns (total 6 columns):\n",
      "id          6 non-null int64\n",
      "date        6 non-null datetime64[ns]\n",
      "city        6 non-null object\n",
      "category    6 non-null object\n",
      "age         6 non-null int64\n",
      "price       4 non-null float64\n",
      "dtypes: datetime64[ns](1), float64(1), int64(2), object(2)\n",
      "memory usage: 368.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5、每一列数据的格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                   int64\n",
       "date        datetime64[ns]\n",
       "city                object\n",
       "category            object\n",
       "age                  int64\n",
       "price              float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6、category列格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['category'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7、空值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id   date   city category    age  price\n",
       "0  False  False  False    False  False  False\n",
       "1  False  False  False    False  False   True\n",
       "2  False  False  False    False  False  False\n",
       "3  False  False  False    False  False  False\n",
       "4  False  False  False    False  False   True\n",
       "5  False  False  False    False  False  False"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8、查看price列空值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1     True\n",
       "2    False\n",
       "3    False\n",
       "4     True\n",
       "5    False\n",
       "Name: price, dtype: bool"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['price'].isnull()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9、查看age列的唯一值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([23, 44, 54, 32, 34, 45], dtype=int64)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['age'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10、查看数据表的值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[1001, Timestamp('2013-01-02 00:00:00'), 'Beijing ', '100-A', 23,\n",
       "        1200.9],\n",
       "       [1002, Timestamp('2013-01-03 00:00:00'), 'SH', '100-B', 44, nan],\n",
       "       [1003, Timestamp('2013-01-04 00:00:00'), ' guangzhou ', '110-A',\n",
       "        54, 2133.0],\n",
       "       [1004, Timestamp('2013-01-05 00:00:00'), 'Shenzhen', '110-C', 32,\n",
       "        5433.2],\n",
       "       [1005, Timestamp('2013-01-06 00:00:00'), 'shanghai', '210-A', 34,\n",
       "        nan],\n",
       "       [1006, Timestamp('2013-01-07 00:00:00'), 'BEIJING ', '130-F', 45,\n",
       "        4432.0]], dtype=object)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11、查看列名称"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'date', 'city', 'category', 'age', 'price'], dtype='object')"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 二、数据表清晰"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1、使用price的均值对NA进行填充"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>Beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>SH</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>Shenzhen</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45</td>\n",
       "      <td>4432.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date         city category  age   price\n",
       "0  1001 2013-01-02     Beijing     100-A   23  1200.9\n",
       "1  1002 2013-01-03           SH    100-B   44   100.0\n",
       "2  1003 2013-01-04   guangzhou     110-A   54  2133.0\n",
       "3  1004 2013-01-05     Shenzhen    110-C   32  5433.2\n",
       "4  1005 2013-01-06     shanghai    210-A   34   100.0\n",
       "5  1006 2013-01-07     BEIJING     130-F   45  4432.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.fillna(value=100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1200.900\n",
       "1    3299.775\n",
       "2    2133.000\n",
       "3    5433.200\n",
       "4    3299.775\n",
       "5    4432.000\n",
       "Name: price, dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['price'].fillna(df['price'].mean())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2、清除city字段的字符前后空格,字符的大小写转换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>SH</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>SHENZHEN</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>SHANGHAI</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45</td>\n",
       "      <td>4432.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date       city category  age   price\n",
       "0  1001 2013-01-02    BEIJING    100-A   23  1200.9\n",
       "1  1002 2013-01-03         SH    100-B   44     NaN\n",
       "2  1003 2013-01-04  GUANGZHOU    110-A   54  2133.0\n",
       "3  1004 2013-01-05   SHENZHEN    110-C   32  5433.2\n",
       "4  1005 2013-01-06   SHANGHAI    210-A   34     NaN\n",
       "5  1006 2013-01-07    BEIJING    130-F   45  4432.0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['city']=df['city'].map(str.strip).map(str.upper)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3、更改数据格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1200\n",
       "1     100\n",
       "2    2133\n",
       "3    5433\n",
       "4     100\n",
       "5    4432\n",
       "Name: price, dtype: int32"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['price'].fillna(value=100).astype('int')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4、更改列名称"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>SH</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>SHENZHEN</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>SHANGHAI</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45</td>\n",
       "      <td>4432.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date       city category-size  age   price\n",
       "0  1001 2013-01-02    BEIJING         100-A   23  1200.9\n",
       "1  1002 2013-01-03         SH         100-B   44     NaN\n",
       "2  1003 2013-01-04  GUANGZHOU         110-A   54  2133.0\n",
       "3  1004 2013-01-05   SHENZHEN         110-C   32  5433.2\n",
       "4  1005 2013-01-06   SHANGHAI         210-A   34     NaN\n",
       "5  1006 2013-01-07    BEIJING         130-F   45  4432.0"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.rename(columns={'category': 'category-size'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5、删除后出现的重复值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      BEIJING\n",
       "1           SH\n",
       "2    GUANGZHOU\n",
       "3     SHENZHEN\n",
       "4     SHANGHAI\n",
       "Name: city, dtype: object"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['city'].drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6、数据替换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>SHENZHEN</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>SHANGHAI</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45</td>\n",
       "      <td>4432.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date       city category  age   price\n",
       "0  1001 2013-01-02    BEIJING    100-A   23  1200.9\n",
       "1  1002 2013-01-03   shanghai    100-B   44     NaN\n",
       "2  1003 2013-01-04  GUANGZHOU    110-A   54  2133.0\n",
       "3  1004 2013-01-05   SHENZHEN    110-C   32  5433.2\n",
       "4  1005 2013-01-06   SHANGHAI    210-A   34     NaN\n",
       "5  1006 2013-01-07    BEIJING    130-F   45  4432.0"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['city'] = df['city'].replace('SH', 'shanghai')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 三、数据预处理"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1、内连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>m-point</th>\n",
       "      <th>pay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.9</td>\n",
       "      <td>male</td>\n",
       "      <td>10</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133.0</td>\n",
       "      <td>male</td>\n",
       "      <td>20</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>SHENZHEN</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433.2</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>SHANGHAI</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>male</td>\n",
       "      <td>40</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45</td>\n",
       "      <td>4432.0</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date       city category  age   price  gender  m-point pay\n",
       "0  1001 2013-01-02    BEIJING    100-A   23  1200.9    male       10   Y\n",
       "1  1002 2013-01-03   shanghai    100-B   44     NaN  female       12   N\n",
       "2  1003 2013-01-04  GUANGZHOU    110-A   54  2133.0    male       20   Y\n",
       "3  1004 2013-01-05   SHENZHEN    110-C   32  5433.2  female       40   Y\n",
       "4  1005 2013-01-06   SHANGHAI    210-A   34     NaN    male       40   N\n",
       "5  1006 2013-01-07    BEIJING    130-F   45  4432.0  female       40   Y"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner=pd.merge(df,df1,how='inner')\n",
    "df_inner"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2、左连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>m-point</th>\n",
       "      <th>pay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.9</td>\n",
       "      <td>male</td>\n",
       "      <td>10</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133.0</td>\n",
       "      <td>male</td>\n",
       "      <td>20</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>SHENZHEN</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433.2</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>SHANGHAI</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>male</td>\n",
       "      <td>40</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45</td>\n",
       "      <td>4432.0</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date       city category  age   price  gender  m-point pay\n",
       "0  1001 2013-01-02    BEIJING    100-A   23  1200.9    male       10   Y\n",
       "1  1002 2013-01-03   shanghai    100-B   44     NaN  female       12   N\n",
       "2  1003 2013-01-04  GUANGZHOU    110-A   54  2133.0    male       20   Y\n",
       "3  1004 2013-01-05   SHENZHEN    110-C   32  5433.2  female       40   Y\n",
       "4  1005 2013-01-06   SHANGHAI    210-A   34     NaN    male       40   N\n",
       "5  1006 2013-01-07    BEIJING    130-F   45  4432.0  female       40   Y"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_left = pd.merge(df, df1, how='left')\n",
    "df_left"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3、右连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>m-point</th>\n",
       "      <th>pay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001.0</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1200.9</td>\n",
       "      <td>male</td>\n",
       "      <td>10</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002.0</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003.0</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54.0</td>\n",
       "      <td>2133.0</td>\n",
       "      <td>male</td>\n",
       "      <td>20</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004.0</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>SHENZHEN</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32.0</td>\n",
       "      <td>5433.2</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005.0</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>SHANGHAI</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>male</td>\n",
       "      <td>40</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006.0</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45.0</td>\n",
       "      <td>4432.0</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1007.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>male</td>\n",
       "      <td>30</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>108.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>20</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id       date       city category   age   price  gender  m-point pay\n",
       "0  1001.0 2013-01-02    BEIJING    100-A  23.0  1200.9    male       10   Y\n",
       "1  1002.0 2013-01-03   shanghai    100-B  44.0     NaN  female       12   N\n",
       "2  1003.0 2013-01-04  GUANGZHOU    110-A  54.0  2133.0    male       20   Y\n",
       "3  1004.0 2013-01-05   SHENZHEN    110-C  32.0  5433.2  female       40   Y\n",
       "4  1005.0 2013-01-06   SHANGHAI    210-A  34.0     NaN    male       40   N\n",
       "5  1006.0 2013-01-07    BEIJING    130-F  45.0  4432.0  female       40   Y\n",
       "6  1007.0        NaT        NaN      NaN   NaN     NaN    male       30   N\n",
       "7   108.0        NaT        NaN      NaN   NaN     NaN  female       20   Y"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_right = pd.merge(df, df1, how='right')\n",
    "df_right"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4、外连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>m-point</th>\n",
       "      <th>pay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001.0</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1200.9</td>\n",
       "      <td>male</td>\n",
       "      <td>10</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002.0</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003.0</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54.0</td>\n",
       "      <td>2133.0</td>\n",
       "      <td>male</td>\n",
       "      <td>20</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004.0</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>SHENZHEN</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32.0</td>\n",
       "      <td>5433.2</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005.0</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>SHANGHAI</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>male</td>\n",
       "      <td>40</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006.0</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45.0</td>\n",
       "      <td>4432.0</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1007.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>male</td>\n",
       "      <td>30</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>108.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>20</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id       date       city category   age   price  gender  m-point pay\n",
       "0  1001.0 2013-01-02    BEIJING    100-A  23.0  1200.9    male       10   Y\n",
       "1  1002.0 2013-01-03   shanghai    100-B  44.0     NaN  female       12   N\n",
       "2  1003.0 2013-01-04  GUANGZHOU    110-A  54.0  2133.0    male       20   Y\n",
       "3  1004.0 2013-01-05   SHENZHEN    110-C  32.0  5433.2  female       40   Y\n",
       "4  1005.0 2013-01-06   SHANGHAI    210-A  34.0     NaN    male       40   N\n",
       "5  1006.0 2013-01-07    BEIJING    130-F  45.0  4432.0  female       40   Y\n",
       "6  1007.0        NaT        NaN      NaN   NaN     NaN    male       30   N\n",
       "7   108.0        NaT        NaN      NaN   NaN     NaN  female       20   Y"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_outer = pd.merge(df, df1, how='outer')\n",
    "df_outer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5、设置索引列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>m-point</th>\n",
       "      <th>pay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001.0</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1200.9</td>\n",
       "      <td>male</td>\n",
       "      <td>10</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004.0</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>SHENZHEN</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32.0</td>\n",
       "      <td>5433.2</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005.0</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>SHANGHAI</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>male</td>\n",
       "      <td>40</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002.0</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006.0</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45.0</td>\n",
       "      <td>4432.0</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003.0</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54.0</td>\n",
       "      <td>2133.0</td>\n",
       "      <td>male</td>\n",
       "      <td>20</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1007.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>male</td>\n",
       "      <td>30</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>108.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>20</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id       date       city category   age   price  gender  m-point pay\n",
       "0  1001.0 2013-01-02    BEIJING    100-A  23.0  1200.9    male       10   Y\n",
       "3  1004.0 2013-01-05   SHENZHEN    110-C  32.0  5433.2  female       40   Y\n",
       "4  1005.0 2013-01-06   SHANGHAI    210-A  34.0     NaN    male       40   N\n",
       "1  1002.0 2013-01-03   shanghai    100-B  44.0     NaN  female       12   N\n",
       "5  1006.0 2013-01-07    BEIJING    130-F  45.0  4432.0  female       40   Y\n",
       "2  1003.0 2013-01-04  GUANGZHOU    110-A  54.0  2133.0    male       20   Y\n",
       "6  1007.0        NaT        NaN      NaN   NaN     NaN    male       30   N\n",
       "7   108.0        NaT        NaN      NaN   NaN     NaN  female       20   Y"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_outer.set_index('id')\n",
    "df_outer = df_outer.sort_values('age')\n",
    "df_outer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6、按字段信息增加辅助列（如果price列的值>3000，group列显示high，否则显示low）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>m-point</th>\n",
       "      <th>pay</th>\n",
       "      <th>group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001.0</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1200.9</td>\n",
       "      <td>male</td>\n",
       "      <td>10</td>\n",
       "      <td>Y</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004.0</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>SHENZHEN</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32.0</td>\n",
       "      <td>5433.2</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005.0</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>SHANGHAI</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>male</td>\n",
       "      <td>40</td>\n",
       "      <td>N</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002.0</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006.0</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45.0</td>\n",
       "      <td>4432.0</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003.0</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54.0</td>\n",
       "      <td>2133.0</td>\n",
       "      <td>male</td>\n",
       "      <td>20</td>\n",
       "      <td>Y</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1007.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>male</td>\n",
       "      <td>30</td>\n",
       "      <td>N</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>108.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>20</td>\n",
       "      <td>Y</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id       date       city category   age   price  gender  m-point pay  \\\n",
       "0  1001.0 2013-01-02    BEIJING    100-A  23.0  1200.9    male       10   Y   \n",
       "3  1004.0 2013-01-05   SHENZHEN    110-C  32.0  5433.2  female       40   Y   \n",
       "4  1005.0 2013-01-06   SHANGHAI    210-A  34.0     NaN    male       40   N   \n",
       "1  1002.0 2013-01-03   shanghai    100-B  44.0     NaN  female       12   N   \n",
       "5  1006.0 2013-01-07    BEIJING    130-F  45.0  4432.0  female       40   Y   \n",
       "2  1003.0 2013-01-04  GUANGZHOU    110-A  54.0  2133.0    male       20   Y   \n",
       "6  1007.0        NaT        NaN      NaN   NaN     NaN    male       30   N   \n",
       "7   108.0        NaT        NaN      NaN   NaN     NaN  female       20   Y   \n",
       "\n",
       "  group  \n",
       "0   low  \n",
       "3  high  \n",
       "4   low  \n",
       "1   low  \n",
       "5  high  \n",
       "2   low  \n",
       "6   low  \n",
       "7   low  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_outer['group'] = np.where(df_outer['price'] > 3000,'high','low')\n",
    "df_outer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7、对复合多个条件的数据进行分组标记"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>m-point</th>\n",
       "      <th>pay</th>\n",
       "      <th>sign</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.9</td>\n",
       "      <td>male</td>\n",
       "      <td>10</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133.0</td>\n",
       "      <td>male</td>\n",
       "      <td>20</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>SHENZHEN</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433.2</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>SHANGHAI</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>male</td>\n",
       "      <td>40</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45</td>\n",
       "      <td>4432.0</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date       city category  age   price  gender  m-point pay  sign\n",
       "0  1001 2013-01-02    BEIJING    100-A   23  1200.9    male       10   Y   NaN\n",
       "1  1002 2013-01-03   shanghai    100-B   44     NaN  female       12   N   NaN\n",
       "2  1003 2013-01-04  GUANGZHOU    110-A   54  2133.0    male       20   Y   NaN\n",
       "3  1004 2013-01-05   SHENZHEN    110-C   32  5433.2  female       40   Y   NaN\n",
       "4  1005 2013-01-06   SHANGHAI    210-A   34     NaN    male       40   N   NaN\n",
       "5  1006 2013-01-07    BEIJING    130-F   45  4432.0  female       40   Y   1.0"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.loc[(df_inner['city'].map(str.lower) == 'beijing') & (df_inner['price'] >= 4000), 'sign'] = 1\n",
    "df_inner"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8、对category字段的值依次进行分列，并创建数据表，索引值为df_inner的索引列，列名称为category和size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>category</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>110</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>110</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>210</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>130</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  category size\n",
       "0      100    A\n",
       "1      100    B\n",
       "2      110    A\n",
       "3      110    C\n",
       "4      210    A\n",
       "5      130    F"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "split = pd.DataFrame(\n",
    "        (x.split('-') for x in df_inner['category']),\n",
    "        index=df_inner.index,\n",
    "        columns=['category','size'])\n",
    "split"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9、将完成分裂后的数据表和原df_inner数据表进行匹配"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category_x</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>m-point</th>\n",
       "      <th>pay</th>\n",
       "      <th>sign</th>\n",
       "      <th>category_y</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.9</td>\n",
       "      <td>male</td>\n",
       "      <td>10</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133.0</td>\n",
       "      <td>male</td>\n",
       "      <td>20</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>SHENZHEN</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433.2</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>SHANGHAI</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>male</td>\n",
       "      <td>40</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>210</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45</td>\n",
       "      <td>4432.0</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "      <td>1.0</td>\n",
       "      <td>130</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date       city category_x  age   price  gender  m-point pay  \\\n",
       "0  1001 2013-01-02    BEIJING      100-A   23  1200.9    male       10   Y   \n",
       "1  1002 2013-01-03   shanghai      100-B   44     NaN  female       12   N   \n",
       "2  1003 2013-01-04  GUANGZHOU      110-A   54  2133.0    male       20   Y   \n",
       "3  1004 2013-01-05   SHENZHEN      110-C   32  5433.2  female       40   Y   \n",
       "4  1005 2013-01-06   SHANGHAI      210-A   34     NaN    male       40   N   \n",
       "5  1006 2013-01-07    BEIJING      130-F   45  4432.0  female       40   Y   \n",
       "\n",
       "   sign category_y size  \n",
       "0   NaN        100    A  \n",
       "1   NaN        100    B  \n",
       "2   NaN        110    A  \n",
       "3   NaN        110    C  \n",
       "4   NaN        210    A  \n",
       "5   1.0        130    F  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner=pd.merge(df_inner,split,right_index=True, left_index=True)\n",
    "df_inner"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 四、数据提取"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1、按索引提取单行的数值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                           1001\n",
       "date          2013-01-02 00:00:00\n",
       "city                      BEIJING\n",
       "category_x                  100-A\n",
       "age                            23\n",
       "price                      1200.9\n",
       "gender                       male\n",
       "m-point                        10\n",
       "pay                             Y\n",
       "sign                          NaN\n",
       "category_y                    100\n",
       "size                            A\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.loc[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2、按索引提取区域行数值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category_x</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>m-point</th>\n",
       "      <th>pay</th>\n",
       "      <th>sign</th>\n",
       "      <th>category_y</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.9</td>\n",
       "      <td>male</td>\n",
       "      <td>10</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date      city category_x  age   price  gender  m-point pay  \\\n",
       "0  1001 2013-01-02   BEIJING      100-A   23  1200.9    male       10   Y   \n",
       "1  1002 2013-01-03  shanghai      100-B   44     NaN  female       12   N   \n",
       "\n",
       "   sign category_y size  \n",
       "0   NaN        100    A  \n",
       "1   NaN        100    B  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.iloc[0:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3、重设索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category_x</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>m-point</th>\n",
       "      <th>pay</th>\n",
       "      <th>sign</th>\n",
       "      <th>category_y</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.9</td>\n",
       "      <td>male</td>\n",
       "      <td>10</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133.0</td>\n",
       "      <td>male</td>\n",
       "      <td>20</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>SHENZHEN</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433.2</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>SHANGHAI</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>male</td>\n",
       "      <td>40</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>210</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45</td>\n",
       "      <td>4432.0</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "      <td>1.0</td>\n",
       "      <td>130</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index    id       date       city category_x  age   price  gender  m-point  \\\n",
       "0      0  1001 2013-01-02    BEIJING      100-A   23  1200.9    male       10   \n",
       "1      1  1002 2013-01-03   shanghai      100-B   44     NaN  female       12   \n",
       "2      2  1003 2013-01-04  GUANGZHOU      110-A   54  2133.0    male       20   \n",
       "3      3  1004 2013-01-05   SHENZHEN      110-C   32  5433.2  female       40   \n",
       "4      4  1005 2013-01-06   SHANGHAI      210-A   34     NaN    male       40   \n",
       "5      5  1006 2013-01-07    BEIJING      130-F   45  4432.0  female       40   \n",
       "\n",
       "  pay  sign category_y size  \n",
       "0   Y   NaN        100    A  \n",
       "1   N   NaN        100    B  \n",
       "2   Y   NaN        110    A  \n",
       "3   Y   NaN        110    C  \n",
       "4   N   NaN        210    A  \n",
       "5   Y   1.0        130    F  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4、设置日期为索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>category_x</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>m-point</th>\n",
       "      <th>pay</th>\n",
       "      <th>sign</th>\n",
       "      <th>category_y</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>1001</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.9</td>\n",
       "      <td>male</td>\n",
       "      <td>10</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>1002</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>1003</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133.0</td>\n",
       "      <td>male</td>\n",
       "      <td>20</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-05</th>\n",
       "      <td>1004</td>\n",
       "      <td>SHENZHEN</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433.2</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>1005</td>\n",
       "      <td>SHANGHAI</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>male</td>\n",
       "      <td>40</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>210</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-07</th>\n",
       "      <td>1006</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45</td>\n",
       "      <td>4432.0</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "      <td>1.0</td>\n",
       "      <td>130</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id       city category_x  age   price  gender  m-point pay  \\\n",
       "date                                                                       \n",
       "2013-01-02  1001    BEIJING      100-A   23  1200.9    male       10   Y   \n",
       "2013-01-03  1002   shanghai      100-B   44     NaN  female       12   N   \n",
       "2013-01-04  1003  GUANGZHOU      110-A   54  2133.0    male       20   Y   \n",
       "2013-01-05  1004   SHENZHEN      110-C   32  5433.2  female       40   Y   \n",
       "2013-01-06  1005   SHANGHAI      210-A   34     NaN    male       40   N   \n",
       "2013-01-07  1006    BEIJING      130-F   45  4432.0  female       40   Y   \n",
       "\n",
       "            sign category_y size  \n",
       "date                              \n",
       "2013-01-02   NaN        100    A  \n",
       "2013-01-03   NaN        100    B  \n",
       "2013-01-04   NaN        110    A  \n",
       "2013-01-05   NaN        110    C  \n",
       "2013-01-06   NaN        210    A  \n",
       "2013-01-07   1.0        130    F  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner=df_inner.set_index('date') \n",
    "df_inner"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5、提取1月4日之前所有的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>category_x</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>m-point</th>\n",
       "      <th>pay</th>\n",
       "      <th>sign</th>\n",
       "      <th>category_y</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>1001</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.9</td>\n",
       "      <td>male</td>\n",
       "      <td>10</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>1002</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>1003</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133.0</td>\n",
       "      <td>male</td>\n",
       "      <td>20</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id       city category_x  age   price  gender  m-point pay  \\\n",
       "date                                                                       \n",
       "2013-01-02  1001    BEIJING      100-A   23  1200.9    male       10   Y   \n",
       "2013-01-03  1002   shanghai      100-B   44     NaN  female       12   N   \n",
       "2013-01-04  1003  GUANGZHOU      110-A   54  2133.0    male       20   Y   \n",
       "\n",
       "            sign category_y size  \n",
       "date                              \n",
       "2013-01-02   NaN        100    A  \n",
       "2013-01-03   NaN        100    B  \n",
       "2013-01-04   NaN        110    A  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner[:'2013-01-04']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6、使用iloc按位置区域提取数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6.1冒号前后的数字不再是索引的标签名称，而是数据所在的位置，从0开始，前三行，前两列。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>1001</td>\n",
       "      <td>BEIJING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>1002</td>\n",
       "      <td>shanghai</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id      city\n",
       "date                      \n",
       "2013-01-02  1001   BEIJING\n",
       "2013-01-03  1002  shanghai"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.iloc[:2,:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6.2提取第0、2、5行，4、5列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>1200.9</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>2133.0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-07</th>\n",
       "      <td>4432.0</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             price  gender\n",
       "date                      \n",
       "2013-01-02  1200.9    male\n",
       "2013-01-04  2133.0    male\n",
       "2013-01-07  4432.0  female"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.iloc[[0,2,5],[4,5]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7、使用ix按索引标签和位置混合提取数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2013-01-03号之前，前四列数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>category_x</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>1001</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>1002</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id      city category_x  age\n",
       "date                                      \n",
       "2013-01-02  1001   BEIJING      100-A   23\n",
       "2013-01-03  1002  shanghai      100-B   44"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.ix[:'2013-01-03',:4]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8、判断city列的值是否为北京"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date\n",
       "2013-01-02     True\n",
       "2013-01-03    False\n",
       "2013-01-04    False\n",
       "2013-01-05    False\n",
       "2013-01-06    False\n",
       "2013-01-07     True\n",
       "Name: city, dtype: bool"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner['city'].isin(['BEIJING'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9、判断city列里是否包含beijing和shanghai，然后将符合条件的数据提取出来'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>category_x</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>m-point</th>\n",
       "      <th>pay</th>\n",
       "      <th>sign</th>\n",
       "      <th>category_y</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>1001</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.9</td>\n",
       "      <td>male</td>\n",
       "      <td>10</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>1002</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-07</th>\n",
       "      <td>1006</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45</td>\n",
       "      <td>4432.0</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "      <td>1.0</td>\n",
       "      <td>130</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id      city category_x  age   price  gender  m-point pay  sign  \\\n",
       "date                                                                            \n",
       "2013-01-02  1001   BEIJING      100-A   23  1200.9    male       10   Y   NaN   \n",
       "2013-01-03  1002  shanghai      100-B   44     NaN  female       12   N   NaN   \n",
       "2013-01-07  1006   BEIJING      130-F   45  4432.0  female       40   Y   1.0   \n",
       "\n",
       "           category_y size  \n",
       "date                        \n",
       "2013-01-02        100    A  \n",
       "2013-01-03        100    B  \n",
       "2013-01-07        130    F  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.loc[df_inner['city'].isin(['BEIJING','shanghai'])]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9、提取前三个字符，并生成数据表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>130</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  category\n",
       "0      100\n",
       "1      100\n",
       "2      110\n",
       "3      110\n",
       "4      210\n",
       "5      130"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = pd.DataFrame(df['category'].str[:3])\n",
    "test"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 五、数据筛选"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1、找出年龄大于25并且在beijing的记录"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>age</th>\n",
       "      <th>category</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>45</td>\n",
       "      <td>130-F</td>\n",
       "      <td>4432.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id     city  age category   price\n",
       "5  1006  BEIJING   45    130-F  4432.0"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[\n",
    "        (df['age'] > 25) & (df['city'].str.lower() == 'beijing'), \n",
    "        ['id','city','age','category','price']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2、找出年龄大于35或者在beijing的记录'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>age</th>\n",
       "      <th>category</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>23</td>\n",
       "      <td>100-A</td>\n",
       "      <td>1200.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>44</td>\n",
       "      <td>100-B</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>54</td>\n",
       "      <td>110-A</td>\n",
       "      <td>2133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>45</td>\n",
       "      <td>130-F</td>\n",
       "      <td>4432.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       city  age category   price\n",
       "0  1001    BEIJING   23    100-A  1200.9\n",
       "1  1002   shanghai   44    100-B     NaN\n",
       "2  1003  GUANGZHOU   54    110-A  2133.0\n",
       "5  1006    BEIJING   45    130-F  4432.0"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[\n",
    "        (df['age'] > 35) | (df['city'].str.lower() == 'beijing'), \n",
    "        ['id','city','age','category','price']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3、找出不在beijing的记录,对筛选后的数据按city列进行计数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>age</th>\n",
       "      <th>category</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>44</td>\n",
       "      <td>100-B</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>54</td>\n",
       "      <td>110-A</td>\n",
       "      <td>2133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>SHENZHEN</td>\n",
       "      <td>32</td>\n",
       "      <td>110-C</td>\n",
       "      <td>5433.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>SHANGHAI</td>\n",
       "      <td>34</td>\n",
       "      <td>210-A</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       city  age category   price\n",
       "1  1002   shanghai   44    100-B     NaN\n",
       "2  1003  GUANGZHOU   54    110-A  2133.0\n",
       "3  1004   SHENZHEN   32    110-C  5433.2\n",
       "4  1005   SHANGHAI   34    210-A     NaN"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = df.loc[\n",
    "            (df['city'].str.lower() != 'beijing'), \n",
    "            ['id','city','age','category','price']]\n",
    "test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'总数：4'"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'总数：'+str(test.sort_values(['age'],ascending = False).city.count())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4、使用query函数进行筛选"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45</td>\n",
       "      <td>4432.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date      city category  age   price\n",
       "0  1001 2013-01-02   BEIJING    100-A   23  1200.9\n",
       "1  1002 2013-01-03  shanghai    100-B   44     NaN\n",
       "5  1006 2013-01-07   BEIJING    130-F   45  4432.0"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('city == [\"BEIJING\", \"shanghai\"]')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5、对筛选后的结果按prince进行求和"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5632.9"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('city == [\"BEIJING\", \"shanghai\"]').price.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 六、数据汇总"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1、对所有的列进行计数汇总"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>BEIJING</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GUANGZHOU</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SHANGHAI</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SHENZHEN</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shanghai</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           id  date  category  age  price\n",
       "city                                     \n",
       "BEIJING     2     2         2    2      2\n",
       "GUANGZHOU   1     1         1    1      1\n",
       "SHANGHAI    1     1         1    1      0\n",
       "SHENZHEN    1     1         1    1      1\n",
       "shanghai    1     1         1    1      0"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('city').count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2、按城市对id字段进行计数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "city\n",
       "BEIJING      2\n",
       "GUANGZHOU    1\n",
       "SHANGHAI     1\n",
       "SHENZHEN     1\n",
       "shanghai     1\n",
       "Name: id, dtype: int64"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('city')['id'].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3、对两个字段进行汇总计数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "city       age\n",
       "BEIJING    23     1\n",
       "           45     1\n",
       "GUANGZHOU  54     1\n",
       "SHANGHAI   34     1\n",
       "SHENZHEN   32     1\n",
       "shanghai   44     1\n",
       "Name: id, dtype: int64"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['city','age'])['id'].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4、对city字段进行汇总，并分别计算prince的合计和均值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>len</th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>BEIJING</th>\n",
       "      <td>2.0</td>\n",
       "      <td>5632.9</td>\n",
       "      <td>2816.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GUANGZHOU</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2133.0</td>\n",
       "      <td>2133.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SHANGHAI</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SHENZHEN</th>\n",
       "      <td>1.0</td>\n",
       "      <td>5433.2</td>\n",
       "      <td>5433.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shanghai</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           len     sum     mean\n",
       "city                           \n",
       "BEIJING    2.0  5632.9  2816.45\n",
       "GUANGZHOU  1.0  2133.0  2133.00\n",
       "SHANGHAI   1.0     NaN      NaN\n",
       "SHENZHEN   1.0  5433.2  5433.20\n",
       "shanghai   1.0     NaN      NaN"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('city')['price'].agg([len,np.sum, np.mean])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 七、数据统计"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1、简单的数据采样"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>category_x</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>m-point</th>\n",
       "      <th>pay</th>\n",
       "      <th>sign</th>\n",
       "      <th>category_y</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-05</th>\n",
       "      <td>1004</td>\n",
       "      <td>SHENZHEN</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433.2</td>\n",
       "      <td>female</td>\n",
       "      <td>40</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>1003</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133.0</td>\n",
       "      <td>male</td>\n",
       "      <td>20</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>1005</td>\n",
       "      <td>SHANGHAI</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>male</td>\n",
       "      <td>40</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>210</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id       city category_x  age   price  gender  m-point pay  \\\n",
       "date                                                                       \n",
       "2013-01-05  1004   SHENZHEN      110-C   32  5433.2  female       40   Y   \n",
       "2013-01-04  1003  GUANGZHOU      110-A   54  2133.0    male       20   Y   \n",
       "2013-01-06  1005   SHANGHAI      210-A   34     NaN    male       40   N   \n",
       "\n",
       "            sign category_y size  \n",
       "date                              \n",
       "2013-01-05   NaN        110    C  \n",
       "2013-01-04   NaN        110    A  \n",
       "2013-01-06   NaN        210    A  "
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.sample(n=3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2、手动设置采样权重"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45</td>\n",
       "      <td>4432.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date       city category  age   price\n",
       "2  1003 2013-01-04  GUANGZHOU    110-A   54  2133.0\n",
       "5  1006 2013-01-07    BEIJING    130-F   45  4432.0"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weights = [0, 0.1, 0.7, 0, 0.2, 0.1]\n",
    "df.sample(n=2, weights=weights)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3、采样后不放回"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>SHENZHEN</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45</td>\n",
       "      <td>4432.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>SHANGHAI</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date       city category  age   price\n",
       "0  1001 2013-01-02    BEIJING    100-A   23  1200.9\n",
       "3  1004 2013-01-05   SHENZHEN    110-C   32  5433.2\n",
       "2  1003 2013-01-04  GUANGZHOU    110-A   54  2133.0\n",
       "5  1006 2013-01-07    BEIJING    130-F   45  4432.0\n",
       "1  1002 2013-01-03   shanghai    100-B   44     NaN\n",
       "4  1005 2013-01-06   SHANGHAI    210-A   34     NaN"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sample(n=6, replace=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4、采样后放回"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>SHENZHEN</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45</td>\n",
       "      <td>4432.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45</td>\n",
       "      <td>4432.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date      city category  age   price\n",
       "0  1001 2013-01-02   BEIJING    100-A   23  1200.9\n",
       "1  1002 2013-01-03  shanghai    100-B   44     NaN\n",
       "1  1002 2013-01-03  shanghai    100-B   44     NaN\n",
       "3  1004 2013-01-05  SHENZHEN    110-C   32  5433.2\n",
       "5  1006 2013-01-07   BEIJING    130-F   45  4432.0\n",
       "5  1006 2013-01-07   BEIJING    130-F   45  4432.0"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sample(n=6, replace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5、数据表描述性统计"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### round函数设置显示小数位，T表示转置"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <td>6.0</td>\n",
       "      <td>1003.50</td>\n",
       "      <td>1.87</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>1002.25</td>\n",
       "      <td>1003.5</td>\n",
       "      <td>1004.75</td>\n",
       "      <td>1006.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age</th>\n",
       "      <td>6.0</td>\n",
       "      <td>38.67</td>\n",
       "      <td>11.09</td>\n",
       "      <td>23.0</td>\n",
       "      <td>32.50</td>\n",
       "      <td>39.0</td>\n",
       "      <td>44.75</td>\n",
       "      <td>54.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>price</th>\n",
       "      <td>4.0</td>\n",
       "      <td>3299.78</td>\n",
       "      <td>1966.39</td>\n",
       "      <td>1200.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5433.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       count     mean      std     min      25%     50%      75%     max\n",
       "id       6.0  1003.50     1.87  1001.0  1002.25  1003.5  1004.75  1006.0\n",
       "age      6.0    38.67    11.09    23.0    32.50    39.0    44.75    54.0\n",
       "price    4.0  3299.78  1966.39  1200.9      NaN     NaN      NaN  5433.2"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe().round(2).T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6、计算列的标准差"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1966.3905891675404"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['price'].std()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7、计算两个字段间的协方差"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "nan"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['price'].cov(df_inner['age'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8、数据表中所有字段间的协方差"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <td>3.500000</td>\n",
       "      <td>5.800000</td>\n",
       "      <td>3.242617e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age</th>\n",
       "      <td>5.800000</td>\n",
       "      <td>123.066667</td>\n",
       "      <td>2.646583e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>price</th>\n",
       "      <td>3242.616667</td>\n",
       "      <td>2646.583333</td>\n",
       "      <td>3.866692e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                id          age         price\n",
       "id        3.500000     5.800000  3.242617e+03\n",
       "age       5.800000   123.066667  2.646583e+03\n",
       "price  3242.616667  2646.583333  3.866692e+06"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.cov()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9、两个字段的相关性分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "nan"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['price'].corr(df_inner['age'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10、数据表的相关性分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.279463</td>\n",
       "      <td>0.792163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age</th>\n",
       "      <td>0.279463</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.098074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>price</th>\n",
       "      <td>0.792163</td>\n",
       "      <td>0.098074</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             id       age     price\n",
       "id     1.000000  0.279463  0.792163\n",
       "age    0.279463  1.000000  0.098074\n",
       "price  0.792163  0.098074  1.000000"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.corr()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 八、数据输出"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据导出到Excel、CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.to_excel('excel_to_python.xls',sheet_name='test1')\n",
    "df.to_csv('excel_to_python.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1001</th>\n",
       "      <th>2013-01-02</th>\n",
       "      <th>BEIJING</th>\n",
       "      <th>100-A</th>\n",
       "      <th>23</th>\n",
       "      <th>1200.9</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>GUANGZHOU</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>SHENZHEN</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>SHANGHAI</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>45</td>\n",
       "      <td>4432.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   0  1001  2013-01-02    BEIJING  100-A  23  1200.9\n",
       "0  1  1002  2013-01-03   shanghai  100-B  44     NaN\n",
       "1  2  1003  2013-01-04  GUANGZHOU  110-A  54  2133.0\n",
       "2  3  1004  2013-01-05   SHENZHEN  110-C  32  5433.2\n",
       "3  4  1005  2013-01-06   SHANGHAI  210-A  34     NaN\n",
       "4  5  1006  2013-01-07    BEIJING  130-F  45  4432.0"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(pd.read_csv('excel_to_python.csv',header=1))\n",
    "df"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "celltoolbar": "Raw Cell Format",
  "kernelspec": {
   "display_name": "Python [Anaconda3]",
   "language": "python",
   "name": "Python [Anaconda3]"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
